{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import csv  "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "31. How to fill an intermittent time series so all missing dates show up with values of previous non-missing date?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000-01-01     1.0\n",
       "2000-01-02    10.0\n",
       "2000-01-03    10.0\n",
       "2000-01-04     3.0\n",
       "2000-01-05     3.0\n",
       "2000-01-06     3.0\n",
       "2000-01-07     3.0\n",
       "2000-01-08     3.0\n",
       "Freq: D, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser = pd.Series([1,10,3, np.nan], index=pd.to_datetime(['2000-01-01', '2000-01-03', '2000-01-06', '2000-01-08']))\n",
    "\n",
    "# Solution\n",
    "ser.resample('D').ffill()  # fill with previous value\n",
    "\n",
    "# Alternatives\n",
    "ser.resample('D').bfill()  # fill with next value\n",
    "ser.resample('D').bfill().ffill()  # fill next else prev value"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "32. How to compute the autocorrelations of a numeric series?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.38, 0.22, 0.19, 0.28, 0.28, -0.09, -0.43, -0.11, -0.05, -0.38]\n",
      "Lag having highest correlation:  7\n"
     ]
    }
   ],
   "source": [
    "ser = pd.Series(np.arange(20) + np.random.normal(1, 10, 20))\n",
    "\n",
    "# Solution\n",
    "autocorrelations = [ser.autocorr(i).round(2) for i in range(11)]\n",
    "print(autocorrelations[1:])\n",
    "print('Lag having highest correlation: ', np.argmax(np.abs(autocorrelations[1:]))+1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "33. How to import only every nth row from a csv file to create a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      crim  zn  indus chas    nox     rm   age     dis rad  tax ptratio  \\\n",
      "0  0.21977   0   6.91    0  0.448  5.602    62  6.0877   3  233    17.9   \n",
      "1   0.0686   0   2.89    0  0.445  7.416  62.5  3.4952   2  276      18   \n",
      "2  2.73397   0  19.58    0  0.871  5.597  94.9  1.5257   5  403    14.7   \n",
      "3   0.0315  95   1.47    0  0.403  6.975  15.3  7.6534   3  402      17   \n",
      "4  0.19073  22   5.86    0  0.431  6.718  17.5  7.8265   7  330    19.1   \n",
      "\n",
      "        b  lstat  medv  \n",
      "0   396.9   16.2  19.4  \n",
      "1   396.9   6.19  33.2  \n",
      "2  351.85  21.45  15.4  \n",
      "3   396.9   4.56  34.9  \n",
      "4  393.74   6.56  26.2  \n"
     ]
    }
   ],
   "source": [
    "# Solution 2: Use chunks and list comprehension\n",
    "df = pd.read_csv('BostonHousing.csv', chunksize=50)\n",
    "df2 = pd.concat([chunk.iloc[0] for chunk in df], axis=1)\n",
    "df2 = df2.transpose()\n",
    "\n",
    "# Solution 3: Use csv reader\n",
    "        \n",
    "with open('BostonHousing.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    out = []\n",
    "    for i, row in enumerate(reader):\n",
    "        if i%50 == 0:\n",
    "            out.append(row)\n",
    "\n",
    "df2 = pd.DataFrame(out[1:], columns=out[0])\n",
    "print(df2.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "34. How to change column values when importing csv to a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      crim  zn indus chas    nox     rm   age     dis rad  tax ptratio  \\\n",
      "0  0.00632  18  2.31    0  0.538  6.575  65.2    4.09   1  296    15.3   \n",
      "1  0.02731   0  7.07    0  0.469  6.421  78.9  4.9671   2  242    17.8   \n",
      "2  0.02729   0  7.07    0  0.469  7.185  61.1  4.9671   2  242    17.8   \n",
      "3  0.03237   0  2.18    0  0.458  6.998  45.8  6.0622   3  222    18.7   \n",
      "4  0.06905   0  2.18    0  0.458  7.147  54.2  6.0622   3  222    18.7   \n",
      "\n",
      "        b lstat  medv  \n",
      "0   396.9  4.98   Low  \n",
      "1   396.9  9.14   Low  \n",
      "2  392.83  4.03  High  \n",
      "3  394.63  2.94  High  \n",
      "4   396.9  5.33  High  \n"
     ]
    }
   ],
   "source": [
    "# Solution 1: Using converter parameter\n",
    "df = pd.read_csv('BostonHousing.csv', \n",
    "                 converters={'medv': lambda x: 'High' if float(x) > 25 else 'Low'})\n",
    "\n",
    "\n",
    "# Solution 2: Using csv reader\n",
    "import csv\n",
    "with open('BostonHousing.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    out = []\n",
    "    for i, row in enumerate(reader):\n",
    "        if i > 0:\n",
    "            row[13] = 'High' if float(row[13]) > 25 else 'Low'\n",
    "        out.append(row)\n",
    "\n",
    "df = pd.DataFrame(out[1:], columns=out[0])\n",
    "print(df.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "35. How to create a dataframe with rows as strides from a given series?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 0,  1,  2,  3],\n",
       "       [ 2,  3,  4,  5],\n",
       "       [ 4,  5,  6,  7],\n",
       "       [ 6,  7,  8,  9],\n",
       "       [ 8,  9, 10, 11],\n",
       "       [10, 11, 12, 13]], dtype=int64)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L = pd.Series(range(15))\n",
    "\n",
    "def gen_strides(a, stride_len=5, window_len=5):\n",
    "    n_strides = ((a.size-window_len)//stride_len) + 1\n",
    "    return np.array([a[s:(s+window_len)] for s in np.arange(0, a.size, stride_len)[:n_strides]])\n",
    "\n",
    "gen_strides(L, stride_len=2, window_len=4)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "36. How to import only specified columns from a csv file?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      crim  medv\n",
      "0  0.00632  24.0\n",
      "1  0.02731  21.6\n",
      "2  0.02729  34.7\n",
      "3  0.03237  33.4\n",
      "4  0.06905  36.2\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('BostonHousing.csv', usecols=['crim', 'medv'])\n",
    "print(df.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "37. How to get the nrows, ncolumns, datatype, summary stats of each column of a dataframe? Also get the array and list equivalent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(93, 27)\n",
      "Manufacturer           object\n",
      "Model                  object\n",
      "Type                   object\n",
      "Min.Price             float64\n",
      "Price                 float64\n",
      "Max.Price             float64\n",
      "MPG.city              float64\n",
      "MPG.highway           float64\n",
      "AirBags                object\n",
      "DriveTrain             object\n",
      "Cylinders              object\n",
      "EngineSize            float64\n",
      "Horsepower            float64\n",
      "RPM                   float64\n",
      "Rev.per.mile          float64\n",
      "Man.trans.avail        object\n",
      "Fuel.tank.capacity    float64\n",
      "Passengers            float64\n",
      "Length                float64\n",
      "Wheelbase             float64\n",
      "Width                 float64\n",
      "Turn.circle           float64\n",
      "Rear.seat.room        float64\n",
      "Luggage.room          float64\n",
      "Weight                float64\n",
      "Origin                 object\n",
      "Make                   object\n",
      "dtype: object\n",
      "float64    18\n",
      "object      9\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "#  number of rows and columns\n",
    "print(df.shape)\n",
    "\n",
    "# datatypes\n",
    "print(df.dtypes)\n",
    "\n",
    "# how many columns under each dtype\n",
    "#print(df.get_dtype_counts())\n",
    "print(df.dtypes.value_counts())\n",
    "\n",
    "# summary statistics\n",
    "df_stats = df.describe()\n",
    "\n",
    "# numpy array \n",
    "df_arr = df.values\n",
    "\n",
    "# list\n",
    "df_list = df.values.tolist()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "38. How to extract the row and column number of a particular cell with given criterion?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "61.9\n",
      "Price\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "# Get Manufacturer with highest price\n",
    "df.loc[df.Price == np.max(df.Price), ['Manufacturer', 'Model', 'Type']]\n",
    "\n",
    "# Get Row and Column number\n",
    "row, col = np.where(df.values == np.max(df.Price))\n",
    "\n",
    "# Get the value\n",
    "df.iat[row[0], col[0]]\n",
    "df.iloc[row[0], col[0]]\n",
    "\n",
    "# Alternates\n",
    "print(df.at[row[0], 'Price'])\n",
    "print(df.get(row[0], 'Price'))\n",
    "\n",
    "# The difference between `iat` - `iloc` vs `at` - `loc` is:\n",
    "# `iat` snd `iloc` accepts row and column numbers. \n",
    "# Whereas `at` and `loc` accepts index and column names."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "39. How to rename a specific columns in a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Manufacturer', 'Model', 'CarType', 'Min_Price', 'Price', 'Max_Price',\n",
      "       'MPG_city', 'MPG_highway', 'AirBags', 'DriveTrain', 'Cylinders',\n",
      "       'EngineSize', 'Horsepower', 'RPM', 'Rev_per_mile', 'Man_trans_avail',\n",
      "       'Fuel_tank_capacity', 'Passengers', 'Length', 'Wheelbase', 'Width',\n",
      "       'Turn_circle', 'Rear_seat_room', 'Luggage_room', 'Weight', 'Origin',\n",
      "       'Make'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "# Step 1:\n",
    "df=df.rename(columns = {'Type':'CarType'})\n",
    "# or\n",
    "df.columns.values[2] = \"CarType\"\n",
    "\n",
    "# Step 2:\n",
    "df.columns = df.columns.map(lambda x: x.replace('.', '_'))\n",
    "print(df.columns)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "40. How to check if a dataframe has any missing values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "df.isnull().values.any()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "41. How to count the number of missing values in each column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "n_missings_each_col = df.apply(lambda x: x.isnull().sum())\n",
    "n_missings_each_col.argmax()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "42. How to replace missing values of multiple numeric columns with the mean?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Min.Price  Max.Price\n",
      "0  12.900000  18.800000\n",
      "1  29.200000  38.700000\n",
      "2  25.900000  32.300000\n",
      "3  17.118605  44.600000\n",
      "4  17.118605  21.459091\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "df_out = df[['Min.Price', 'Max.Price']] = df[['Min.Price', 'Max.Price']].apply(lambda x: x.fillna(x.mean()))\n",
    "print(df_out.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "43. How to use apply function on existing columns with global variables as additional arguments?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "d = {'Min.Price': np.nanmean, 'Max.Price': np.nanmedian}\n",
    "df[['Min.Price', 'Max.Price']] = df[['Min.Price', 'Max.Price']].apply(lambda x, d: x.fillna(d[x.name](x)), args=(d, ))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "44. How to select a specific column from a dataframe as a dataframe instead of a series?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.arange(20).reshape(-1, 5), columns=list('abcde'))\n",
    "\n",
    "# Solution\n",
    "type(df[['a']])\n",
    "type(df.loc[:, ['a']])\n",
    "type(df.iloc[:, [0]])\n",
    "\n",
    "# Alternately the following returns a Series\n",
    "type(df.a)\n",
    "type(df['a'])\n",
    "type(df.loc[:, 'a'])\n",
    "type(df.iloc[:, 1])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "45. How to change the order of columns of a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.arange(20).reshape(-1, 5), columns=list('abcde'))\n",
    "\n",
    "# Solution Q1\n",
    "df[list('cbade')]\n",
    "\n",
    "# Solution Q2 - No hard coding\n",
    "def switch_columns(df, col1=None, col2=None):\n",
    "    colnames = df.columns.tolist()\n",
    "    i1, i2 = colnames.index(col1), colnames.index(col2)\n",
    "    colnames[i2], colnames[i1] = colnames[i1], colnames[i2]\n",
    "    return df[colnames]\n",
    "\n",
    "df1 = switch_columns(df, 'a', 'c')\n",
    "\n",
    "# Solution Q3\n",
    "df[sorted(df.columns)]\n",
    "# or\n",
    "df.sort_index(axis=1, ascending=False, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "46. How to set the number of rows and columns displayed in the output?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "pd.set_option('display.max_columns', 10)\n",
    "pd.set_option('display.max_rows', 10)\n",
    "# df\n",
    "\n",
    "# Show all available options\n",
    "# pd.describe_option()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "47. How to format or suppress scientific notations in a pandas dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   random\n",
      "0  0.0603\n",
      "1  0.2242\n",
      "2  0.5164\n",
      "3  0.5704\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.random(4)**10, columns=['random'])\n",
    "\n",
    "# Solution 1: Rounding\n",
    "df.round(4)\n",
    "\n",
    "# Solution 2: Use apply to change format\n",
    "df.apply(lambda x: '%.4f' % x, axis=1)\n",
    "# or\n",
    "df.applymap(lambda x: '%.4f' % x)\n",
    "\n",
    "# Solution 3: Use set_option\n",
    "pd.set_option('display.float_format', lambda x: '%.4f' % x)\n",
    "\n",
    "# Solution 4: Assign display.float_format\n",
    "pd.options.display.float_format = '{:.4f}'.format\n",
    "print(df)\n",
    "\n",
    "# Reset/undo float formatting\n",
    "pd.options.display.float_format = None"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "48. How to format all the values in a dataframe as percentages?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "</style>\n",
       "<table id=\"T_203b2\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th id=\"T_203b2_level0_col0\" class=\"col_heading level0 col0\" >random</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_203b2_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_203b2_row0_col0\" class=\"data row0 col0\" >48.93%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_203b2_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_203b2_row1_col0\" class=\"data row1 col0\" >48.93%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_203b2_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_203b2_row2_col0\" class=\"data row2 col0\" >68.31%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_203b2_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_203b2_row3_col0\" class=\"data row3 col0\" >18.54%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x20d6ead10c0>"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.random(4), columns=['random'])\n",
    "\n",
    "# Solution\n",
    "out = df.style.format({\n",
    "    'random': '{0:.2%}'.format,\n",
    "})\n",
    "\n",
    "out\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "49. How to filter every nth row in a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Manufacturer    Model     Type\n",
      "0         Acura  Integra    Small\n",
      "20     Chrysler  LeBaron  Compact\n",
      "40        Honda  Prelude   Sporty\n",
      "60      Mercury   Cougar  Midsize\n",
      "80       Subaru   Loyale    Small\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv')\n",
    "\n",
    "# Solution\n",
    "print(df.iloc[::20, :][['Manufacturer', 'Model', 'Type']])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "50. How to create a primary key index by combining relevant columns?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.read_csv('Cars93_miss.csv', usecols=[0,1,2,3,5])\n",
    "\n",
    "# Solution\n",
    "df[['Manufacturer', 'Model', 'Type']] = df[['Manufacturer', 'Model', 'Type']].fillna('missing')\n",
    "df.index = df.Manufacturer + '_' + df.Model + '_' + df.Type\n",
    "print(df.index.is_unique)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "51. How to get the row number of the nth largest value in a column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1, 30, 30).reshape(10,-1), columns=list('abc'))\n",
    "\n",
    "# Solution\n",
    "n = 5\n",
    "df['a'].argsort()[::-1][n]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "52. How to find the position of the nth largest value greater than a given value?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ser:  [10, 96, 7, 18, 12, 47, 95, 27, 26, 44, 9, 85, 66, 54, 19] mean:  41\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "ser = pd.Series(np.random.randint(1, 100, 15))\n",
    "\n",
    "# Solution\n",
    "print('ser: ', ser.tolist(), 'mean: ', round(ser.mean()))\n",
    "# np.argwhere(ser > ser.mean())[1]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "53. How to get the last n rows of a dataframe with row sum > 100?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(10, 40, 60).reshape(-1, 4))\n",
    "\n",
    "# Solution\n",
    "# print row sums\n",
    "rowsums = df.apply(np.sum, axis=1)\n",
    "\n",
    "# last two rows with row sum greater than 100\n",
    "last_two_rows = df.iloc[np.where(rowsums > 100)[0][-2:], :]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "54. How to find and cap outliers from a series or dataframe column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.05 %ile:  0.016049294076965887 | 0.95 %ile:  63.87667222018393\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "ser = pd.Series(np.logspace(-2, 2, 30))\n",
    "\n",
    "# Solution\n",
    "def cap_outliers(ser, low_perc, high_perc):\n",
    "    low, high = ser.quantile([low_perc, high_perc])\n",
    "    print(low_perc, '%ile: ', low, '|', high_perc, '%ile: ', high)\n",
    "    ser[ser < low] = low\n",
    "    ser[ser > high] = high\n",
    "    return(ser)\n",
    "\n",
    "capped_ser = cap_outliers(ser, .05, .95)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "55. How to reshape a dataframe to the largest possible square after removing the negative values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1   2   3   4   5   6   7   8   9\n",
      "0  18 -14   1  24  47  -8  33 -10  39  49\n",
      "1  27  27  25  -2   8 -20   6  26 -10  20\n",
      "2 -11  31  48  24   3  -1 -17 -10  32  41\n",
      "3  42  11  44  38  15   9 -14  -1  -4  -7\n",
      "4  11 -18  43  32  23  24  40  29  -1 -14\n",
      "5  18  48   3  18  28 -14  17  -4  45  33\n",
      "6  28   2  31  -6  14  18 -10  14  32  48\n",
      "7  -4  24   5  41 -17   9 -20 -16 -18   7\n",
      "8  37 -16 -13  24  -2 -12  38  26  14  17\n",
      "9  40  49  13  20   6   2 -11  -8  16  -9\n",
      "[[18. 24. 47. 33. 39. 49. 27. 27.]\n",
      " [25.  8.  6. 26. 20. 31. 48. 24.]\n",
      " [ 3. 32. 41. 42. 11. 44. 38. 15.]\n",
      " [ 9. 11. 43. 32. 23. 24. 40. 29.]\n",
      " [18. 48.  3. 18. 28. 17. 45. 33.]\n",
      " [28. 31. 14. 18. 14. 32. 48. 24.]\n",
      " [ 5. 41.  9.  7. 37. 24. 38. 26.]\n",
      " [14. 17. 40. 49. 13. 20.  6. 16.]]\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(-20, 50, 100).reshape(10,-1))\n",
    "print(df)\n",
    "\n",
    "# Solution\n",
    "# Step 1: remove negative values from arr\n",
    "arr = df[df > 0].values.flatten()\n",
    "arr_qualified = arr[~np.isnan(arr)]\n",
    "\n",
    "# Step 2: find side-length of largest possible square\n",
    "n = int(np.floor(arr_qualified.shape[0]**.5))\n",
    "\n",
    "# Step 3: Take top n^2 items without changing positions\n",
    "top_indexes = np.argsort(arr_qualified)[::-1]\n",
    "output = np.take(arr_qualified, sorted(top_indexes[:n**2])).reshape(n, -1)\n",
    "print(output)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "56. How to swap two rows of a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1   2   3   4\n",
      "0   0   1   2   3   4\n",
      "1  10  11  12  13  14\n",
      "2   5   6   7   8   9\n",
      "3  15  16  17  18  19\n",
      "4  20  21  22  23  24\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.arange(25).reshape(5, -1))\n",
    "\n",
    "# Solution\n",
    "def swap_rows(df, i1, i2):\n",
    "    a, b = df.iloc[i1, :].copy(), df.iloc[i2, :].copy()\n",
    "    df.iloc[i1, :], df.iloc[i2, :] = b, a\n",
    "    return df\n",
    "\n",
    "print(swap_rows(df, 1, 2))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "57. How to reverse the rows of a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1   2   3   4\n",
      "4  20  21  22  23  24\n",
      "3  15  16  17  18  19\n",
      "2  10  11  12  13  14\n",
      "1   5   6   7   8   9\n",
      "0   0   1   2   3   4\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.arange(25).reshape(5, -1))\n",
    "\n",
    "# Solution 1\n",
    "df.iloc[::-1, :]\n",
    "\n",
    "# Solution 2\n",
    "print(df.loc[df.index[::-1], :])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "58. How to create one-hot encodings of a categorical variable (dummy variables)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   0  5  10  15  20   b   c   d   e\n",
      "0  1  0   0   0   0   1   2   3   4\n",
      "1  0  1   0   0   0   6   7   8   9\n",
      "2  0  0   1   0   0  11  12  13  14\n",
      "3  0  0   0   1   0  16  17  18  19\n",
      "4  0  0   0   0   1  21  22  23  24\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.arange(25).reshape(5,-1), columns=list('abcde'))\n",
    "\n",
    "# Solution\n",
    "df_onehot = pd.concat([pd.get_dummies(df['a']), df[list('bcde')]], axis=1)\n",
    "print(df_onehot)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "59. Which column contains the highest number of row-wise maximum values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column with highest row maxes:  3\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 40).reshape(10, -1))\n",
    "\n",
    "# Solution\n",
    "print('Column with highest row maxes: ', df.apply(np.argmax, axis=1).value_counts().index[0])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "60. How to create a new column that contains the row number of nearest column by euclidean distance?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>p</th>\n",
       "      <th>q</th>\n",
       "      <th>r</th>\n",
       "      <th>s</th>\n",
       "      <th>nearest_row</th>\n",
       "      <th>dist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>48</td>\n",
       "      <td>18</td>\n",
       "      <td>j</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>57</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>82</td>\n",
       "      <td>h</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>63</td>\n",
       "      <td>8</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>i</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>33</td>\n",
       "      <td>84</td>\n",
       "      <td>69</td>\n",
       "      <td>98</td>\n",
       "      <td>i</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>77</td>\n",
       "      <td>61</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>i</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>35</td>\n",
       "      <td>71</td>\n",
       "      <td>71</td>\n",
       "      <td>29</td>\n",
       "      <td>b</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>27</td>\n",
       "      <td>94</td>\n",
       "      <td>i</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>h</th>\n",
       "      <td>11</td>\n",
       "      <td>68</td>\n",
       "      <td>50</td>\n",
       "      <td>18</td>\n",
       "      <td>c</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>d</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>j</th>\n",
       "      <td>58</td>\n",
       "      <td>90</td>\n",
       "      <td>42</td>\n",
       "      <td>91</td>\n",
       "      <td>i</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    p   q   r   s nearest_row  dist\n",
       "a  19  30  48  18           j   102\n",
       "b  57  20  11  82           h   100\n",
       "c  63   8  49  90           i   114\n",
       "d  33  84  69  98           i   133\n",
       "e  77  61  45  60           i   107\n",
       "f  35  71  71  29           b    97\n",
       "g  12  31  27  94           i    94\n",
       "h  11  68  50  18           c   107\n",
       "i   6  21   5   3           d   133\n",
       "j  58  90  42  91           i   129"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.randint(1,100, 40).reshape(10, -1), columns=list('pqrs'), index=list('abcdefghij'))\n",
    "\n",
    "# Solution\n",
    "import numpy as np\n",
    "\n",
    "# init outputs\n",
    "nearest_rows = []\n",
    "nearest_distance = []\n",
    "\n",
    "# iterate rows.\n",
    "for i, row in df.iterrows():\n",
    "    curr = row\n",
    "    rest = df.drop(i)\n",
    "    e_dists = {}  # init dict to store euclidean dists for current row.\n",
    "    # iterate rest of rows for current row\n",
    "    for j, contestant in rest.iterrows():\n",
    "        # compute euclidean dist and update e_dists\n",
    "        e_dists.update({j: round(np.linalg.norm(curr.values - contestant.values))})\n",
    "    # update nearest row to current row and the distance value\n",
    "    nearest_rows.append(max(e_dists, key=e_dists.get))\n",
    "    nearest_distance.append(max(e_dists.values()))\n",
    "\n",
    "df['nearest_row'] = nearest_rows\n",
    "df['dist'] = nearest_distance\n",
    "df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "61. How to know the maximum possible correlation value of each column against other columns?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Maximum Correlation possible for each column:  [0.75 0.78 0.56 0.78 0.51 0.88 0.7  0.7  0.75 0.88]\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 80).reshape(8, -1), columns=list('pqrstuvwxy'), index=list('abcdefgh'))\n",
    "df\n",
    "\n",
    "# Solution\n",
    "abs_corrmat = np.abs(df.corr())\n",
    "max_corr = abs_corrmat.apply(lambda x: sorted(x)[-2])\n",
    "print('Maximum Correlation possible for each column: ', np.round(max_corr.tolist(), 2))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "62. How to create a column containing the minimum by maximum of each row?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 80).reshape(8, -1))\n",
    "\n",
    "# Solution 1\n",
    "min_by_max = df.apply(lambda x: np.min(x)/np.max(x), axis=1)\n",
    "\n",
    "# Solution 2\n",
    "min_by_max = np.min(df, axis=1)/np.max(df, axis=1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "63. How to create a column that contains the penultimate value in each row?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1   2   3   4  ...   6   7   8   9  penultimate\n",
      "0  29  62  16  22  14  ...  78  99  57  64           78\n",
      "1  57  85  36  14  70  ...  96  97  56  27           96\n",
      "2  69   3  56  51  41  ...  95  77  12  47           77\n",
      "3  55  31   2  27  15  ...  48  91  36  20           55\n",
      "4  53  57  24  20  51  ...  10   3  28  38           57\n",
      "5   1  12  30  42   6  ...  52  41  73  62           62\n",
      "6  87  21   1  36   5  ...  54  55  79  88           88\n",
      "7   1  40  49   8  63  ...  33  54  54  66           63\n",
      "\n",
      "[8 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 80).reshape(8, -1))\n",
    "\n",
    "# Solution\n",
    "out = df.apply(lambda x: x.sort_values().unique()[-2], axis=1)\n",
    "df['penultimate'] = out\n",
    "print(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "64. How to normalize all columns in a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Solution Q1\n",
      "       0     1     2     3     4     5     6     7     8     9\n",
      "0 -0.66  1.11  1.28 -1.31 -0.71 -0.84  0.62 -0.04 -0.63  0.85\n",
      "1  1.36 -1.53  1.60 -0.60 -0.71  0.29 -0.11  0.86  0.73 -0.14\n",
      "2  1.33 -1.28 -0.27  0.22 -0.41 -1.55  1.18 -0.59 -1.20 -0.82\n",
      "3  0.15  0.72 -0.37  0.05 -0.12  0.75  1.06  1.76 -0.04 -0.72\n",
      "4  0.00 -0.02 -0.80  0.76 -0.09  1.17 -1.67 -1.03 -1.20 -0.72\n",
      "5 -0.55  0.12  0.37 -0.09  1.66 -0.05 -0.27 -0.97 -0.16  1.69\n",
      "6 -1.62 -0.23 -0.64  1.87  1.41  1.08 -1.07 -0.62  1.38  0.85\n",
      "7  0.00  1.11 -1.18 -0.90 -1.03 -0.84  0.26  0.63  1.12 -0.98\n",
      "Solution Q2\n",
      "       0     1     2     3     4     5     6     7     8     9\n",
      "0  0.68  0.00  0.12  1.00  0.88  0.74  0.20  0.65  0.78  0.31\n",
      "1  0.00  1.00  0.00  0.78  0.88  0.32  0.45  0.32  0.25  0.69\n",
      "2  0.01  0.91  0.67  0.52  0.77  1.00  0.00  0.84  1.00  0.94\n",
      "3  0.41  0.15  0.71  0.57  0.66  0.15  0.04  0.00  0.55  0.90\n",
      "4  0.46  0.43  0.87  0.35  0.65  0.00  1.00  1.00  1.00  0.90\n",
      "5  0.64  0.37  0.44  0.62  0.00  0.45  0.51  0.98  0.60  0.00\n",
      "6  1.00  0.51  0.81  0.00  0.09  0.03  0.79  0.85  0.00  0.31\n",
      "7  0.46  0.00  1.00  0.87  1.00  0.74  0.32  0.41  0.10  1.00\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 80).reshape(8, -1))\n",
    "\n",
    "# Solution Q1\n",
    "out1 = df.apply(lambda x: ((x - x.mean())/x.std()).round(2))\n",
    "print('Solution Q1\\n',out1)\n",
    "\n",
    "# Solution Q2\n",
    "out2 = df.apply(lambda x: ((x.max() - x)/(x.max() - x.min())).round(2))\n",
    "print('Solution Q2\\n', out2)  "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "65. How to compute the correlation of each row with the suceeding row?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0.02, 0.11, 0.02, -0.19, 0.11, 0.52, 0.35]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 80).reshape(8, -1))\n",
    "\n",
    "# Solution\n",
    "[df.iloc[i].corr(df.iloc[i+1]).round(2) for i in range(df.shape[0])[:-1]]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "66. How to replace both the diagonals of dataframe with 0?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1,100, 100).reshape(10, -1))\n",
    "\n",
    "# Solution\n",
    "for i in range(df.shape[0]):\n",
    "    df.iat[i, i] = 0\n",
    "    df.iat[df.shape[0]-i-1, i] = 0"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "67. How to get the particular group of a groupby dataframe by key?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    col1      col2  col3\n",
      "0  apple  0.649266     8\n",
      "3  apple  0.684263     3\n",
      "6  apple  0.920632     2\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame({'col1': ['apple', 'banana', 'orange'] * 3,\n",
    "                   'col2': np.random.rand(9),\n",
    "                   'col3': np.random.randint(0, 15, 9)})\n",
    "\n",
    "df_grouped = df.groupby(['col1'])\n",
    "\n",
    "# Solution 1\n",
    "print(df_grouped.get_group('apple'))\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "68. How to get the n’th largest value of a column when grouped by another column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    fruit     taste  price\n",
      "0   apple  0.041078     12\n",
      "1  banana  0.718294      6\n",
      "2  orange  0.125639      1\n",
      "3   apple  0.670311      2\n",
      "4  banana  0.103796     10\n",
      "5  orange  0.810661     10\n",
      "6   apple  0.699865      9\n",
      "7  banana  0.060704      7\n",
      "8  orange  0.400757     13\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.10379590178091136"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame({'fruit': ['apple', 'banana', 'orange'] * 3,\n",
    "                   'taste': np.random.rand(9),\n",
    "                   'price': np.random.randint(0, 15, 9)})\n",
    "\n",
    "print(df)\n",
    "\n",
    "# Solution\n",
    "df_grpd = df['taste'].groupby(df.fruit)\n",
    "df_grpd.get_group('banana').sort_values().iloc[-2]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "69. How to compute grouped mean on pandas dataframe and keep the grouped column as another column (not index)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    fruit      price\n",
      "0   apple   8.000000\n",
      "1  banana   7.666667\n",
      "2  orange  10.000000\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame({'fruit': ['apple', 'banana', 'orange'] * 3,\n",
    "                   'rating': np.random.rand(9),\n",
    "                   'price': np.random.randint(0, 15, 9)})\n",
    "\n",
    "# Solution\n",
    "out = df.groupby('fruit', as_index=False)['price'].mean()\n",
    "print(out)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "70. How to join two dataframes by 2 columns so they have only the common rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fruit</th>\n",
       "      <th>weight</th>\n",
       "      <th>price_left</th>\n",
       "      <th>pazham</th>\n",
       "      <th>kilo</th>\n",
       "      <th>price_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>apple</td>\n",
       "      <td>high</td>\n",
       "      <td>6</td>\n",
       "      <td>apple</td>\n",
       "      <td>high</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>apple</td>\n",
       "      <td>high</td>\n",
       "      <td>7</td>\n",
       "      <td>apple</td>\n",
       "      <td>high</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>apple</td>\n",
       "      <td>high</td>\n",
       "      <td>12</td>\n",
       "      <td>apple</td>\n",
       "      <td>high</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>orange</td>\n",
       "      <td>low</td>\n",
       "      <td>2</td>\n",
       "      <td>orange</td>\n",
       "      <td>low</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>orange</td>\n",
       "      <td>low</td>\n",
       "      <td>1</td>\n",
       "      <td>orange</td>\n",
       "      <td>low</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>orange</td>\n",
       "      <td>low</td>\n",
       "      <td>10</td>\n",
       "      <td>orange</td>\n",
       "      <td>low</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    fruit weight  price_left  pazham  kilo  price_right\n",
       "0   apple   high           6   apple  high            0\n",
       "1   apple   high           7   apple  high            0\n",
       "2   apple   high          12   apple  high            0\n",
       "3  orange    low           2  orange   low           13\n",
       "4  orange    low           1  orange   low           13\n",
       "5  orange    low          10  orange   low           13"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df1 = pd.DataFrame({'fruit': ['apple', 'banana', 'orange'] * 3,\n",
    "                    'weight': ['high', 'medium', 'low'] * 3,\n",
    "                    'price': np.random.randint(0, 15, 9)})\n",
    "\n",
    "df2 = pd.DataFrame({'pazham': ['apple', 'orange', 'pine'] * 2,\n",
    "                    'kilo': ['high', 'low'] * 3,\n",
    "                    'price': np.random.randint(0, 15, 6)})\n",
    "\n",
    "# Solution\n",
    "pd.merge(df1, df2, how='inner', left_on=['fruit', 'weight'], right_on=['pazham', 'kilo'], suffixes=['_left', '_right'])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "71. How to remove rows from a dataframe that are present in another dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    fruit  weight  price\n",
      "2  banana     low      2\n",
      "3   apple    high      3\n",
      "4  orange  medium      4\n",
      "5  banana     low      5\n",
      "6   apple    high      6\n",
      "7  orange  medium      7\n",
      "8  banana     low      8\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df1 = pd.DataFrame({'fruit': ['apple', 'orange', 'banana'] * 3,\n",
    "                    'weight': ['high', 'medium', 'low'] * 3,\n",
    "                    'price': np.arange(9)})\n",
    "\n",
    "df2 = pd.DataFrame({'fruit': ['apple', 'orange', 'pine'] * 2,\n",
    "                    'weight': ['high', 'medium'] * 3,\n",
    "                    'price': np.arange(6)})\n",
    "\n",
    "\n",
    "# Solution\n",
    "print(df1[~df1.isin(df2).all(1)])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "72. How to get the positions where values of two columns match?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([0, 1, 7, 8], dtype=int64),)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame({'fruit1': np.random.choice(['apple', 'orange', 'banana'], 10),\n",
    "                    'fruit2': np.random.choice(['apple', 'orange', 'banana'], 10)})\n",
    "\n",
    "# Solution\n",
    "np.where(df.fruit1 == df.fruit2)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "73. How to create lags and leads of a column in a dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    a   b   c   d  a_lag1  b_lead1\n",
      "0  53  53  91  57     NaN     24.0\n",
      "1  44  24  50  31    53.0     24.0\n",
      "2  32  24  77  60    44.0      6.0\n",
      "3   6   6  51  11    32.0     53.0\n",
      "4  92  53  96  97     6.0      NaN\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1, 100, 20).reshape(-1, 4), columns = list('abcd'))\n",
    "\n",
    "# Solution\n",
    "df['a_lag1'] = df['a'].shift(1)\n",
    "df['b_lead1'] = df['b'].shift(-1)\n",
    "print(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "74. How to get the frequency of unique values in the entire dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input\n",
    "df = pd.DataFrame(np.random.randint(1, 10, 20).reshape(-1, 4), columns = list('abcd'))\n",
    "\n",
    "# Solution\n",
    "pd.value_counts(df.values.ravel())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "75. How to split a text column into two separate columns?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 STD            City    State\n",
      "1  33   Kolkata    West Bengal\n",
      "2  44    Chennai    Tamil Nadu\n",
      "3  40   Hyderabad    Telengana\n",
      "4  80   Bangalore    Karnataka\n"
     ]
    }
   ],
   "source": [
    "# Input\n",
    "df = pd.DataFrame([\"STD, City    State\",\n",
    "\"33, Kolkata    West Bengal\",\n",
    "\"44, Chennai    Tamil Nadu\",\n",
    "\"40, Hyderabad    Telengana\",\n",
    "\"80, Bangalore    Karnataka\"], columns=['row'])\n",
    "\n",
    "# Solution\n",
    "df_out = df.row.str.split(',|\\t', expand=True)\n",
    "\n",
    "# Make first row as header\n",
    "new_header = df_out.iloc[0]\n",
    "df_out = df_out[1:]\n",
    "df_out.columns = new_header\n",
    "print(df_out)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.0"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
